﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using MediaLibraryManagement.Common;
using MediaLibraryManagement.Entity;

namespace MediaLibraryManagement.DAL
{
    public class AlbumDAL : BaseDAL
    {
        #region Properties Implementation

        protected override string TableName { get { return DatabaseTableName.Albums; } }

        #endregion

        public int Insert(Album album)
        {
            int id = 0;
            try
            {
                OpenConnection();
                string commandstring = string.Format("INSERT INTO {0} (Title, ArtistID, Genre, Description, ReleaseDate, Rate, RatingCount, Image, ContributorID) "
                                                            + "OUTPUT INSERTED.ID "
                                                            + "VALUES (@Title, @ArtistID, @Genre, @Description, @ReleaseDate, @Rate, @RatingCount, @Image, @ContributorID)", TableName);
                using (SqlCommand command = new SqlCommand(commandstring, Connection))
                {
                    command.Parameters.AddWithValue("@Title", album.Title);
                    command.Parameters.AddWithValue("@ArtistID", album.ArtistID);
                    command.Parameters.AddWithValue("@Genre", album.Genre);
                    command.Parameters.AddWithValue("@Description", album.Description);
                    command.Parameters.AddWithValue("@ReleaseDate", album.ReleaseDate);
                    command.Parameters.AddWithValue("@Rate", album.Rate);
                    command.Parameters.AddWithValue("@RatingCount", album.RatingCount);
                    command.Parameters.AddWithValue("@Image", Utilities.ImageToByteArray(album.Image));
                    command.Parameters.AddWithValue("@ContributorID", album.ContributorID);
                    id = (int)command.ExecuteScalar();
                }
                CloseConnection();
            }
            catch (Exception ex)
            {
                CloseConnection();
                throw ex;
            }
            return id;
        }

        public void Update(Album album)
        {
            try
            {
                OpenConnection();
                string commandString = string.Format("UPDATE {0} SET "
                                                        + "Title = @Title, ArtistID = @ArtistID, Genre = @Genre, Description = @Description, ReleaseDate = @ReleaseDate, Rate = @Rate, RatingCount = @RatingCount, Image = @Image, ContributorID = @ContributorID "
                                                        + "WHERE ID = {1}"
                                                    , TableName
                                                    , album.ID);
                using (SqlCommand command = new SqlCommand(commandString, Connection))
                {
                    command.Parameters.AddWithValue("@Title", album.Title);
                    command.Parameters.AddWithValue("@ArtistID", album.ArtistID);
                    command.Parameters.AddWithValue("@Genre", album.Genre);
                    command.Parameters.AddWithValue("@Description", album.Description);
                    command.Parameters.AddWithValue("@ReleaseDate", album.ReleaseDate);
                    command.Parameters.AddWithValue("@Rate", album.Rate);
                    command.Parameters.AddWithValue("@RatingCount", album.RatingCount);
                    command.Parameters.AddWithValue("@Image", Utilities.ImageToByteArray(album.Image));
                    command.Parameters.AddWithValue("@ContributorID", album.ContributorID);
                    command.ExecuteNonQuery();
                }
                CloseConnection();
            }
            catch (Exception ex)
            {
                CloseConnection();
                throw ex;
            }
        }

        public List<Album> Search(string title, string genre, string artistName, int year)
        {
            List<Album> albums = new List<Album>();
            try
            {
                OpenConnection();
                string commandString = string.Format("SELECT * FROM {0} "
                                                        + "WHERE Title LIKE '%{1}%' "
                                                        + "AND Genre LIKE '%{2}%' "
                                                        + "AND ArtistID IN (SELECT ID FROM {3} WHERE LOWER((FirstName + ' ' + LastName)) LIKE '%{4}%') "
                                                        , TableName, title, genre, DatabaseTableName.Artists, artistName.ToLowerInvariant());
                if (year > 0)
                {
                    commandString += string.Format("AND YEAR(ReleaseDate) = {0}", year);
                }
                using (SqlCommand command = new SqlCommand(commandString, Connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Album album = (Album)Fetch(reader);
                            albums.Add(album);
                        }
                        reader.Close();
                    }
                }
                CloseConnection();
            }
            catch (Exception ex)
            {
                CloseConnection();
                throw ex;
            }
            return albums;
        }

        #region Private Methods

        protected override IEntity Fetch(IDataReader reader)
        {
            Album album = new Album();
            album.ID = (Int32)reader["ID"];
            album.Title = reader["Title"].ToString();
            album.ArtistID = (Int32)reader["ArtistID"];
            album.Genre = reader["Genre"].ToString();
            album.Description = reader["Description"].ToString();
            album.ReleaseDate = (DateTime)reader["ReleaseDate"];
            album.Rate = float.Parse(reader["Rate"].ToString());
            album.RatingCount = (Int32)reader["RatingCount"];
            album.Image = Utilities.ByteArrayToImage((byte[])reader["Image"]);
            album.ContributorID = (Int32)reader["ContributorID"];
            return album;
        }

        #endregion
    }
}